'''
仪表盘相关的API路由
'''
from flask import Blueprint, jsonify, g
from psycopg2.extras import RealDictCursor
from services.auth_service import login_required
from datetime import datetime, timedelta

# 创建蓝图
dashboard_bp = Blueprint('dashboard', __name__)

# 获取仪表盘数据
@dashboard_bp.route('/dashboard', methods=['GET'])
@login_required
def get_dashboard_data():
    conn = g.db
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    
    # 获取项目总数
    cursor.execute('''
    SELECT COUNT(*) as count 
    FROM projects 
    ''')
    project_count = cursor.fetchone()['count']
    
    # 获取任务总数
    cursor.execute('''
    SELECT COUNT(*) as count 
    FROM main_tasks 
    ''')
    task_count = cursor.fetchone()['count']
    
    # 获取待处理的子任务数
    cursor.execute('''
    SELECT COUNT(*) as count 
    FROM sub_tasks 
    WHERE status = 'pending' 
    AND EXISTS (
        SELECT 1 
        FROM task_breakdown_versions 
        WHERE task_breakdown_versions.id = sub_tasks.breakdown_version_id 
        AND task_breakdown_versions.status = 'active'
    )
    ''')
    pending_task_count = cursor.fetchone()['count']
    
    # 获取已完成的子任务数
    cursor.execute('''
    SELECT COUNT(*) as count 
    FROM sub_tasks 
    WHERE status = 'completed' 
    AND EXISTS (
        SELECT 1 
        FROM task_breakdown_versions 
        WHERE task_breakdown_versions.id = sub_tasks.breakdown_version_id 
        AND task_breakdown_versions.status = 'active'
    )
    ''')
    completed_task_count = cursor.fetchone()['count']
    
    # 获取最近活动
    # 包括：项目创建、任务状态变更、任务拆分等
    cursor.execute('''
    (
        -- 项目创建
        SELECT 
            'project_created' as activity_type, 
            p.name as title,
            p.created_at as time,
            CONCAT('创建了项目 "', p.name, '"') as content
        FROM projects p
        
        UNION ALL
        
        -- 任务状态变更
        SELECT 
            'task_status_changed' as activity_type,
            mt.title,
            mt.updated_at as time,
            CONCAT('任务 "', mt.title, '" 状态变更为 ', mt.status) as content
        FROM main_tasks mt
        
        UNION ALL
        
        -- 子任务完成
        SELECT 
            'subtask_completed' as activity_type,
            st.title,
            st.updated_at as time,
            CONCAT('子任务 "', st.title, '" 已完成') as content
        FROM sub_tasks st
        WHERE st.status = 'completed' AND st.updated_at IS NOT NULL
        
        UNION ALL
        
        -- 任务拆分
        SELECT 
            'task_breakdown' as activity_type,
            mt.title,
            tbv.created_at as time,
            CONCAT('任务 "', mt.title, '" 被拆分为版本 V', tbv.version_number) as content
        FROM task_breakdown_versions tbv
        JOIN main_tasks mt ON tbv.main_task_id = mt.id
    )
    ORDER BY time DESC
    LIMIT 10
    ''')
    
    recent_activities = cursor.fetchall()
    
    # 格式化活动时间
    for activity in recent_activities:
        if activity['time']:
            activity['time'] = activity['time'].strftime('%Y-%m-%d %H:%M')
    
    # 获取任务进度（按项目分组）
    cursor.execute('''
    WITH task_progress AS (
        SELECT 
            p.id as project_id,
            p.name as project_name,
            COUNT(st.id) FILTER (WHERE st.status = 'completed') as completed_tasks,
            COUNT(st.id) as total_tasks
        FROM projects p
        JOIN main_tasks mt ON mt.project_id = p.id
        JOIN task_breakdown_versions tbv ON tbv.main_task_id = mt.id AND tbv.status = 'active'
        JOIN sub_tasks st ON st.breakdown_version_id = tbv.id
        GROUP BY p.id, p.name
    )
    SELECT 
        project_name as name,
        CASE 
            WHEN total_tasks = 0 THEN 0
            ELSE ROUND((completed_tasks::float / total_tasks) * 100)
        END as percentage,
        CASE
            WHEN total_tasks = 0 THEN ''
            WHEN (completed_tasks::float / total_tasks) = 1 THEN 'success'
            WHEN (completed_tasks::float / total_tasks) >= 0.7 THEN ''
            ELSE 'warning'
        END as status
    FROM task_progress
    WHERE total_tasks > 0
    ORDER BY percentage DESC
    LIMIT 5
    ''')
    
    task_progress = cursor.fetchall()
    
    # 组合所有数据
    dashboard_data = {
        'projectCount': project_count,
        'taskCount': task_count,
        'pendingTaskCount': pending_task_count,
        'completedTaskCount': completed_task_count,
        'recentActivities': recent_activities,
        'taskProgress': task_progress
    }
    
    return jsonify({
        'status': 'success',
        'data': dashboard_data
    }) 